﻿
SET QUOTED_IDENTIFIER ON
GO

SET ANSI_NULLS ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_WA_GetAttendanceData]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_WA_GetAttendanceData];
GO
CREATE PROCEDURE [dbo].[sproc_WA_GetAttendanceData] -- 得到时间段内的考勤数据
    @begintime datetime, --查找开始时间 
    @endtime   datetime, --查找结束时间
    @id_xml    xml,
    @idtype    nvarchar(50)   --对应id的类型 "staff","Position","company"

AS
BEGIN
SET NOCOUNT ON
/*
 *
 * XML Format:
 * <array>
    <a value="" />
 * </array>
 *
 */

declare @beginDayValue int;
declare @endDayValue int;

set @beginDayValue = dbo.fn_GetDayValue ( @begintime );
set @endDayValue = dbo.fn_GetDayValue ( @endtime );

if ( @idtype = N'staff' )
begin
    select
        d.*,
        s.*
    from
        dbo.UDS_WorkAttendence_Data d,
        dbo.UDS_Staff s,
        dbo.UDS_WorkAttendence_DaySetting daySetting,
        @id_xml.nodes('/array/a') R(x)
    where
        d.StaffID = s.Staff_ID
        and d.DayValue = daySetting.DayValue
        and daySetting.NeedDuty = 1
        and (daySetting.DayValue between @beginDayValue and @endDayValue)
        and d.StaffID = R.x.value('@value', 'int')
    order by
        d.DayValue asc;
end
else if ( @idtype = N'position' )
begin

    select
        d.*,
        s.* 
    from
        dbo.UDS_WorkAttendence_Data d,
        dbo.UDS_Staff s,
        dbo.UDS_Staff_In_Position sp,
        dbo.UDS_WorkAttendence_DaySetting daySetting,
        @id_xml.nodes('/array/a') R(x)
    where
        d.StaffID = s.Staff_ID
        and d.DayValue = daySetting.DayValue
        and daySetting.NeedDuty = 1
        and (daySetting.DayValue between @beginDayValue and @endDayValue)
        and s.Staff_ID  = sp.Staff_ID
        and sp.Position_ID = R.x.value('@value', 'int')
    order by
        d.DayValue asc;
end
else if ( @idtype = N'company' )
begin
    select
        d.*,
        s.*
    from
        dbo.UDS_WorkAttendence_Data d,
        dbo.UDS_Staff s,
        dbo.UDS_WorkAttendence_DaySetting daySetting
    where
        d.StaffID = s.Staff_ID
        and d.DayValue = daySetting.DayValue
        and daySetting.NeedDuty = 1
        and (daySetting.DayValue between @beginDayValue and @endDayValue)
    order by
        d.DayValue asc;
end

END
GO